var express = require("express");
const { send } = require("express/lib/response");
const myroute = express.Router();
const mySql = require("../model/index");

// 获取个人数据
myroute.get("/personal/get", function (request, response) {
  const page = request.query.page;
  const count = request.query.count;

  const sql = `SELECT COUNT(*),IF(address_id IS NULL,0,COUNT(*)) AS num, group_name,group_id,group_number  FROM address_person RIGHT JOIN address_group ON address_person.person_group=address_group.group_id GROUP BY group_name;`;
  const sql2 = `select count(*) as total from  address_person RIGHT JOIN address_group ON address_person.person_group=address_group.group_id WHERE (CONVERT (NAME USING gbk) >='${request.query.selected}' and CONVERT (NAME USING gbk) <='${request.query.selected1}'  OR CONVERT (NAME USING gbk) LIKE '%${request.query.alphabet}%') AND NAME LIKE '%${request.query.input2}%'  and group_name like '%${request.query.checkGroup}%'`; //查询条数
  const sql3 = `SELECT * FROM address_person RIGHT JOIN address_group  ON address_person.person_group=address_group.group_id WHERE (CONVERT (NAME USING gbk) >='${request.query.selected
    }' and CONVERT (NAME USING gbk) <='${request.query.selected1
    }'  OR NAME LIKE '%${request.query.alphabet}%') AND NAME LIKE '%${request.query.input2
    }%' and group_name like '%${request.query.checkGroup
    }%' order by address_id LIMIT ${(page - 1) * count},${count}`; //查询分页

  mySql.query(sql, (err, data) => {
    if (err) {
      console.log(err);
      response.send({ error: 1, msg: err });
    } else {
      mySql.query(sql2, (err2, data2) => {
        if (err2) {
          console.log(err2);
          response.send({ error: 1, msg: err2 });
        } else {
          mySql.query(sql3, (err3, data3) => {
            if (err3) {
              console.log(err3);
              response.send({ error: 1, msg: err3 });
            } else {
              response.send({
                error: 0,
                msg: "查询成功",
                data: data,
                total: data2[0].total,
                searchDate: data3,
              });
            }
          });
        }
      });
    }
  });
});
// 增加个人通讯录数据
myroute.post("/personal/add", function (request, response) {
  const sql1 = `INSERT INTO address_person VALUES(NULL,'${request.body.name}','${request.body.email}','${request.body.tele}','${request.body.phone}','${request.body.company}','${request.body.group}');`;

  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "添加失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "添加成功",
      data: data,
    });
  });
});
// 删除个人通讯录数据
myroute.post("/personal/del", (req, res) => {
  const sql = `DELETE FROM address_person WHERE address_id in (${req.body.arr});`;
  mySql.query(sql, (err, data) => {
    if (err) {
      res.send("删除失败，请稍后重试！");
    } else {
      res.send("删除成功！");
    }
  });
});
// 修改个人通讯录数据
myroute.post("/personal/move", (req, res) => {
  const sql = `UPDATE address_person SET person_group=${req.body.group}  WHERE address_id in (${req.body.arr});`;

  mySql.query(sql, (err, data) => {
    if (err) {
      res.send("修改失败，请稍后重试！");
    } else {
      res.send("修改成功！");
    }
  });
});

// 获取个人分组数据
myroute.get("/personal/group/get", function (request, response) {
  const sql1 = `SELECT * FROM address_group ;`;
  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "查询失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "查询成功",
      data: data,
    });
  });
});
// 增加个人通讯录分组数据
myroute.post("/personal/group/add", function (request, response) {
  const sql1 = `INSERT INTO address_group VALUES(NULL,'${request.body.name}','${request.body.number}');`;

  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "添加失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "添加成功",
      data: data,
    });
  });
});
// 删除个人通讯录分组数据
myroute.post("/personal/group/del", (req, res) => {
  const sql = `DELETE FROM address_group WHERE group_id in (${req.body.arr});`;
  mySql.query(sql, (err, data) => {
    if (err) {
      res.send("该分组下有信息，不能删除！");
    } else {
      res.send("删除成功！");
    }
  });
});

// 获取公共分组数据
myroute.get("/public/group/get", function (request, response) {
  const sql1 = `SELECT * FROM public_group ;`;
  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "查询失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "查询成功",
      data: data,
    });
  });
});
// 增加公共通讯录分组数据
myroute.post("/public/group/add", function (request, response) {
  const sql1 = `INSERT INTO public_group VALUES(NULL,'${request.body.name}','${request.body.number}','${request.body.note}');`;

  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "添加失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "添加成功",
      data: data,
    });
  });
});
// 删除公共通讯录分组数据
myroute.post("/public/group/del", (req, res) => {
  const sql = `DELETE FROM public_group WHERE group_id in (${req.body.arr});`;
  mySql.query(sql, (err, data) => {
    if (err) {
      res.send("该分组下有信息，不能删除！");
    } else {
      res.send("删除成功！");
    }
  });
});
//获取公共数据
myroute.get("/public/get", function (request, response) {
  const page = request.query.page;
  const count = request.query.count;
  const sql = `SELECT COUNT(*),IF(public_id IS NULL,0,COUNT(*)) AS num,  group_name,group_id,group_num  
FROM public RIGHT JOIN public_group ON public.public_group=public_group.group_id GROUP BY group_name`;
  const sql2 = `select count(*) as total from public  JOIN public_group ON public.public_group=public_group.group_id WHERE public_name LIKE '%${request.query.input2}%' AND group_name LIKE '%${request.query.checkGroup}%'  `; //查询条数
  const sql3 = `SELECT *  FROM public  JOIN public_group ON public.public_group=public_group.group_id WHERE public_name LIKE '%${request.query.input2
    }%' AND group_name LIKE  '%${request.query.checkGroup
    }%' order by public_id LIMIT ${(page - 1) * count},${count}`; //查询分页
  mySql.query(sql, (err, data) => {
    if (err) {
      console.log(err);
      response.send({ error: 1, msg: err });
    } else {
      mySql.query(sql2, (err2, data2) => {
        if (err2) {
          console.log(err2);
          response.send({ error: 1, msg: err2 });
        } else {
          mySql.query(sql3, (err3, data3) => {
            if (err3) {
              console.log(err3);
              response.send({ error: 1, msg: err3 });
            } else {
              response.send({
                error: 0,
                msg: "查询成功",
                data: data,
                total: data2[0].total,
                searchDate: data3,
              });
            }
          });
        }
      });
    }
  });
});
// 增加公共数据
myroute.post("/public/add", function (request, response) {
  const sql1 = `INSERT INTO public VALUES(NULL,'${request.body.name}','${request.body.telt}','${request.body.phone}','${request.body.address}',${request.body.group});`;

  mySql.query(sql1, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "添加失败，请稍后重试",
      });
    }
    response.json({
      code: 200,
      msg: "添加成功",
      data: data,
    });
  });
});
// 删除公共通讯录分组数据
myroute.post("/public/del", (req, res) => {
  const sql = `DELETE FROM public WHERE public_id in (${req.body.arr});`;

  mySql.query(sql, (err, data) => {
    if (err) {
      res.send("删除失败，请稍后重试！");
    } else {
      res.send("删除成功！");
      console.log(data);
    }
  });
});

// 获取单位通讯录

myroute.get("/corporate/get", function (request, response) {
  const page = request.query.page;
  const count = request.query.count;
  const sql = ` SELECT DISTINCT department, company FROM address_company`;
  const sql2 = `SELECT *  FROM address_company WHERE corporate_name LIKE '%${request.query.input
    }%' AND company LIKE  '%${request.query.checkCompany
    }%' and department like '%${request.query.checkGroup
    }%' order by corporate_id LIMIT ${(page - 1) * count},${count}`; //查询分页
  const sql3 = `SELECT count(*) as total  FROM address_company WHERE corporate_name LIKE '%${request.query.input}%' AND (company LIKE  '%${request.query.checkCompany}%' and department like '%${request.query.checkGroup}%')`;

  mySql.query(sql, function (err, data) {
    if (err) {
      return response.json({
        code: 500,
        msg: "查询失败，请稍后重试",
      });
    } else {
      mySql.query(sql2, function (err, data2) {
        if (err) {
          return response.json({
            code: 500,
            msg: "查询失败，请稍后重试",
          });
        } else {
          mySql.query(sql3, function (err3, data3) {
            if (err3) {
              return response.json({
                code: 500,
                msg: err3,
              });
            } else {
              response.send({
                error: 0,
                msg: "查询成功",
                data: data,
                searchData: data2,
                total: data3[0].total,
              });
            }
          });
        }
      });
    }
  });
});
module.exports = myroute;
